package cn.ainit.sys.pagescript;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import cn.ainit.common.ReturnObject;
import cn.ainit.sys.pages.config.entity.ConfigObject;
import cn.ainit.sys.pagescript.entity.ButtonObject;
import cn.ainit.sys.pagescript.entity.ReceiveParam;
import cn.ainit.sys.pagescript.entity.SqlTextAndParams;
import cn.ainit.sys.query.datastore.DataStore;
import cn.ainit.sys.ui.forms.Columns;
import cn.ainit.sys.utils.UUID32;

@Controller
@RequestMapping("/butScript")
public class PageCommonController {
	PageCommonService pageCommonService;
	
	@Resource
	public void setPageCommonService(PageCommonService pageCommonService) {
		this.pageCommonService = pageCommonService;
	}
	
	/****
	 * 前台获取数据自动执行存储过程或者sql语句
	 * @param params
	 * @param butId
	 * @param request
	 * @return
	 */
	@RequestMapping("/butClick")
	@ResponseBody
	public ReturnObject butClick(ReceiveParam params,String butId,HttpServletRequest request){
		ReturnObject returnObject = new ReturnObject();
		for (int i = 0; i < params.getSqlParams().length; i++) {
			System.out.println(params.getSqlParams()[i]);
		}
		ButtonObject buttonObject = null;
		try {
			if (butId != null && !butId.equals("")) {
				buttonObject = pageCommonService.findButObjectById(butId);
			}
			
			//判断执行存储过程 或者sql
			 // 'S;P'('P;S')   先执行 sql 再 执行proc      (先执行proc 再执行    sql  )    
			 // 'S'   只执行sql
			 // 'P'		只执行proc
			if (buttonObject != null ) {
				String[] procOrSql = buttonObject.getSqlOrProc().split(";");
				String[] sqlParamStr = replaceString(params.getSqlParams(),"^^",",");
				String[] procParamStr = replaceString(params.getProcParams(),"^^",",");
				
				for (int i = 0; i < procOrSql.length; i++) {
					if (procOrSql[i].equalsIgnoreCase("S") && (buttonObject.getSqlTxt() != null) && (!buttonObject.getSqlTxt().trim().equals(""))) {
//						pageCommonService.executeSQL();
						//加入判断是否允 修改的该字段
						if (buttonObject.getButType() == null || buttonObject.getButType().equals("") || 
								(buttonObject.getButType().equalsIgnoreCase("C") && buttonObject.getUpdateTable() != null && !buttonObject.getUpdateTable().trim().equals("")) ) {
							SqlTextAndParams sqlText = parseSQL(params.getSqlParams(),buttonObject.getButType(),buttonObject.getUpdateTable());
							System.out.println(sqlText.getSqlText()+"  "+sqlText.getParams().toString());
							String info = pageCommonService.executeSql(sqlText.getSqlText(),sqlText.getParams());
						}else{
							throw new RuntimeException("请设置更新表名");
						}
					}
					
					if (procOrSql[i].equals("P") && (buttonObject.getProcTxt() != null) && (!buttonObject.getProcTxt().trim().equals(""))) {
						if (buttonObject.getProcTxt().split(",").length == procParamStr.length) {
							//System.out.println("PROC参数个数正确");
							String[] msg = pageCommonService.executeProc(buttonObject.getProcTxt(),procParamStr);
						}else{
							throw new RuntimeException("传入PROC参数个数错误");
						}
					}
				}
			}
			returnObject.setInfo("Success");
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setInfo(e.getMessage());
		}
		return returnObject;
	}
	
	/***
	 * 查找字符在字符串中的个数
	 * @param str
	 * @param ch
	 * @return
	 */
	public int find(String str,char ch){
		  int ret = 0;
		  for(int i = 0; i < str.length(); i++)
		    if(str.charAt(i)==ch) ret++;
		  return ret;
	}
	
	/****
	 * 将“^^” 转换成逗号
	 */
	public String[] replaceString(String[] strs,String repStr,String targetStr){
		for (int i = 0; (strs!= null) && (i < strs.length); i++) {
			strs[i] = strs[i].replace(repStr, targetStr);
		}
		return strs;
	}
	
	/***
	 * 前台获取数据，自动保存
	 * 拼接SQL语句  config 包含=号  T.A=1,T.B=2
	 * CRUD(Create Read Update Delete)
	 * @param tableName 
	 */
	public SqlTextAndParams parseSQL(String[] confis,String sqlType, String tableName){  
		SqlTextAndParams sqlText = new SqlTextAndParams();
		StringBuffer sql = new StringBuffer("");
		StringBuffer secSql = new StringBuffer("");
		String[] sqlvalues = new String[confis.length];
		String[] tmpStr;
		String[] pkid;
		System.out.println(confis.length);
		if (sqlType.equals("C")) {//Create
			sql.append(" insert into "+tableName+" (");
			secSql.append(" values (");
			for (int i = 0; i < confis.length; i++) {
				tmpStr = confis[i].split("\\=");
				if (tmpStr[1].equals("-")) {
					tmpStr[1] = "";
				}
				if (tmpStr[0].indexOf(".") >= 0) {
					pkid = tmpStr[0].split("\\.");//user_id.auto=-;
					tmpStr[0] = pkid[0];
					if (pkid[1].equalsIgnoreCase("auto")) {
						tmpStr[1] = UUID32.newGuid();
					}
				}
				sql.append(tmpStr[0]);
				sqlvalues[i] = tmpStr[1];
				secSql.append("?");
				if (i < (confis.length - 1)) {
					sql.append(",");
					secSql.append(",");
				}else{
					sql.append(")");
					secSql.append(")");
				}
			}
			sql.append(secSql);
		}else if (sqlType.equals("R")) {
			
		}else if(sqlType.equals("CU")){
			
		}else if(sqlType.equals("D")){
			
		}
		sqlText.setParams(sqlvalues);
		sqlText.setSqlText(sql.toString());
		return sqlText;
	}
	
/******跳到指定页面************************************************************************************************/
	@RequestMapping("/singleInput")
	public String singleInputPage(String configId,String funsId,HttpServletRequest request){
		request.setAttribute("configId", configId);
		request.setAttribute("funsId",funsId);
		return "/homepages/test/testButClick";
	}
	
	//保存数据
	@RequestMapping("/saveInfo")
	@ResponseBody			//接受参数			按钮ID			按钮权限 			对象ID	
	public ReturnObject saveButClick(ReceiveParam params,String butId,String cright, String configId,HttpServletRequest request){
		ReturnObject returnObject = new ReturnObject();
		try {
			//1.判断按钮权限
			
			//2.获取ds要更新的表
			ConfigObject configObject = pageCommonService.findConfigObj(configId);
			//3.获取所有字段
			//     a.取出主键  设置主键值
			//     b.将界面字段遍历  所有的字段  有则留  添加到相应的字段  
			String colnsIdStr = configObject.getUiColDsId();
			DataStore dataStore = pageCommonService.findDataStoreById(colnsIdStr);
			List<Columns> cpColumns = pageCommonService.findColumnsById(colnsIdStr);
			Map<String,Columns> colnMap = new HashMap<String,Columns>();//数据库中有属性、不是主键字段
			Map<String,Columns> pkColnMap = new HashMap<String,Columns>();//数据库中主键字段
			String[] paramsKandV = params.getSqlParams();//界面参数
			List<String> nameValue = new ArrayList<String>();//返回界面需要填充的参数 【返回界面是的操作】
			//a.取出主键  设置主键值
			for (int i = 0; i < cpColumns.size(); i++) {
				if (cpColumns.get(i).getJavaProperty() != null	&& !cpColumns.get(i).getJavaProperty().equals("")) {
					if (cpColumns.get(i).getPrimaryKey()!=null && cpColumns.get(i).getPrimaryKey().equals("T")) {
						pkColnMap.put(cpColumns.get(i).getJavaProperty(), cpColumns.get(i));
					}else{
						colnMap.put(cpColumns.get(i).getJavaProperty(), cpColumns.get(i));
					}
				}
			}
			// b.将界面字段遍历  所有的字段  有则留  添加到相应的字段  
			//    获取主键生成方式     format = '01'  获取32位码   '02'  sequence  'idStr' sql where id  '04'   '05'    '06'
			StringBuffer keyStr = new StringBuffer("(");//sql字段
			StringBuffer valueStr =  new StringBuffer("(");//占位符
			StringBuffer values = new StringBuffer("");//值的字符串
			String[] tmpKV = new String[2];
			for (int i = 0; i < paramsKandV.length; i++) {
				tmpKV = paramsKandV[i].split("\\=");
				if (colnMap.containsKey(tmpKV[0])) {
					keyStr.append(tmpKV[0]+",");
					valueStr.append("?,");
					String tmpValue = formatValues(colnMap.get(tmpKV[0]).getFormat()==null?"":colnMap.get(tmpKV[0]).getFormat(),tmpKV[1],colnMap.get(tmpKV[0]).getFormatCont());
					values.append(tmpValue+"|");
					if (!tmpValue.equals(tmpKV[1])) {
						nameValue.add(tmpKV[0]+"="+tmpValue);
					}
				}else if (pkColnMap.containsKey(tmpKV[0])) {
					keyStr.append(tmpKV[0]+",");
					valueStr.append("?,");
					String tmpValue = formatValues(pkColnMap.get(tmpKV[0]).getFormat()==null?"":pkColnMap.get(tmpKV[0]).getFormat(),tmpKV[1],pkColnMap.get(tmpKV[0]).getFormatCont());
					values.append(tmpValue+"|");
					if (!tmpValue.equals(tmpKV[1])) {
						nameValue.add(tmpKV[0]+"="+tmpValue);
					}
					pkColnMap.remove(tmpKV[0]);
				}
			}
			//  c.拼接剩余的主键
			for (Iterator it = pkColnMap.entrySet().iterator(); it.hasNext();) {
				Columns columns =  (Columns) it.next();
				keyStr.append(columns.getJavaProperty()+",");
				valueStr.append("?,");
				values.append(formatValues(pkColnMap.get(tmpKV[0]).getFormat(),tmpKV[1],pkColnMap.get(tmpKV[0]).getFormatCont())+"|");
			}
			//拼接SQL
			if (keyStr.toString().equals("")) {
				throw new RuntimeException("没有需要保存的字段，请查看表属性配置！");
			}
			String sqlKeys = keyStr.toString().substring(0, keyStr.toString().length()-1)+")";
			String sqlVals = valueStr.toString().substring(0,valueStr.toString().length()-1)+")";
			String[] valuesArr = values.toString().split("\\|");
			String tableName = dataStore.getUpdateTable();
			String sqlText = " insert into " + tableName + sqlKeys +" values "+sqlVals;
			//执行sql
			boolean flag = pageCommonService.saveObject(sqlText,valuesArr);
			returnObject.setNameValue(nameValue);
			returnObject.setInfo("Success");
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setInfo(e.getMessage());
		}
		return returnObject;
	}
	
	
	/***
	 * format: 01 获取该字段最大值
	 * format：02 sql语句
	 * format: 03 sql语句获取
	 * @param format
	 * @param oldValue
	 * @return
	 */
	public String formatValues(String format,String oldValue,String formatCont) throws Exception{
		String value = null;
		if (format == null || format.trim().equals("")) {
			return oldValue;
		}
		String[] formatArr = format.split(";");
		if (formatArr[0].equals("01")) {
			value = UUID32.newGuid();
		}else if(formatArr[0].equals("02")){
			//FOZT00001
			if (formatArr.length == 2 && !formatArr[1].equals("")) {
				value = pageCommonService.getMaxPkStr(formatArr[1]);
				int index = 0;
				if (value == null || value.equals("")) {
					if (formatCont == null || formatCont.equals("")) {
						throw new RuntimeException("主键类型为设置");
					}
					value = formatCont;
				}
				for(int i = 0; i < value.length(); i++){
					if (value.charAt(i) >= '0' && value.charAt(i) < '9') {
						index = i;
						break;
					}
				}
				String numTmp = String.valueOf(Double.parseDouble(value.substring(index))+1).split("\\.")[0];
				if (numTmp.length() != value.substring(index).length()) {
					int colength = value.substring(index).length()-numTmp.length();
					for (int i = 0; i < colength; i++) {
						numTmp = "0"+numTmp;
					}
				}
				value = value.substring(0, index) + numTmp;
			}else {
				return "";
			}
		}
		return value;
	}
	
	//更新数据
	@RequestMapping("/updateInfo")
	@ResponseBody
	public ReturnObject updateButClick(ReceiveParam params,String butId,String cright, String configId,HttpServletRequest request){
			//3.获取所有字段
			//4.将传入的参数和所有字段比较
			//5.拼接保存sql语句    （加上主键-获取主键生成方式）
		ReturnObject returnObject = new ReturnObject();
		try {
			//1.判断按钮权限
			
			//2.获取ds所更新的表
			ConfigObject configObject = pageCommonService.findConfigObj(configId);
			//3.获取所有字段
			//     a.取出主键  设置主键值
			//     b.将界面字段遍历  所有的字段  有则留  添加到相应的字段  
			String colnsIdStr = configObject.getUiColDsId();
			DataStore dataStore = pageCommonService.findDataStoreById(colnsIdStr);
			List<Columns> cpColumns = pageCommonService.findColumnsById(colnsIdStr);
			Map<String,Columns> colnMap = new HashMap<String,Columns>();//数据库中有属性、可更新字段
			Map<String,Columns> readOnlyColnMap = new HashMap<String,Columns>();//数据库中不可更新字段
			String[] paramsKandV = params.getSqlParams();//界面参数
			//a.取出部不可更新、可更新的字段
			for (int i = 0; i < cpColumns.size(); i++) {
				if (cpColumns.get(i).getJavaProperty() != null	&& !cpColumns.get(i).getJavaProperty().equals("")) {
					if (cpColumns.get(i).getReadOnly()!=null && cpColumns.get(i).getReadOnly().equals("T")) {
						readOnlyColnMap.put(cpColumns.get(i).getJavaProperty(), cpColumns.get(i));
					}else{
						colnMap.put(cpColumns.get(i).getJavaProperty(), cpColumns.get(i));
					}
				}
			}
			// b.将界面字段遍历  所有的字段  有则留  添加到相应的字段  
			//    拼接条件语句 update tableName set k1=v1,k2=v2 where sk1=v1
			StringBuffer keyStr = new StringBuffer("");//sql值字段+占位符
			StringBuffer keyValues = new StringBuffer("");//sql值字段值的字符串
			StringBuffer whereKey =  new StringBuffer("");//条件
			StringBuffer whereKeyValues = new StringBuffer("");//条件值的字符串
			String[] tmpKV = new String[2];
			for (int i = 0; i < paramsKandV.length; i++) {
				tmpKV = paramsKandV[i].split("\\=");
				if (colnMap.containsKey(tmpKV[0])) {//可更新
					keyStr.append(tmpKV[0]+"=?,");
					String tmpValue = parseCheckBox(tmpKV[1]);
					keyValues.append(tmpValue+"|");
				}else if (readOnlyColnMap.containsKey(tmpKV[0])) {//不可更新
					whereKey.append(" and "+tmpKV[0]+"=? ");
					String tmpValue = parseCheckBox(tmpKV[1]);
					whereKeyValues.append(tmpValue+"|");
				}
			}
			//拼接SQL
			if (keyStr.toString().trim().equals("")) {
				throw new RuntimeException("没有修改过的字段");
			}
			if (whereKey.toString().trim().equals("")) {
				throw new RuntimeException("更新记录的条件为空，无法更新操作！");
			}
			String sqlKeys = keyStr.toString().substring(0, keyStr.toString().length()-1);
			String whereSql = whereKey.toString().substring(0,whereKey.toString().length()-1);
			String[] valuesArr = keyValues.toString().split("\\|");
			String[] whereValue = whereKeyValues.toString().split("\\|");
			String tableName = dataStore.getUpdateTable();
			String sqlText = " update " + tableName + " set " +sqlKeys +" where 1=1 "+whereSql;
			//执行sql
			boolean flag = pageCommonService.updateObject(sqlText,valuesArr,whereValue);
			returnObject.setInfo("Success");
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setInfo(e.getMessage());
		}
		return returnObject;	
	}
	/**
	 * 转换界面参数备用方法
	 * @param value
	 * @return
	 */
	public String parseCheckBox(String value){
		return value;
	}
	
}
